﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupLogAll]
#-- Purpose:		Calls spBackupLog for all databases.
#--	Last Update:	01/25/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#-- Readings:		http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupLogAll]
(
	@verify			bit					= 1,
	@label			nvarchar(128)		= NULL
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@startTime datetime, @iCount int, @sError nvarchar(4000), @db_name sysname

--- Initialize the start date 
SELECT	@startTime = GETDATE()

--- Execute the command
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT		db.name
    FROM		master.sys.databases db
    JOIN		master.sys.master_files mf ON db.database_id = mf.database_id
				AND mf.type = 0
	WHERE		db.name NOT IN (SELECT database_name FROM dbo.vwDatabase_DoNotBackup)
    GROUP BY	db.name
    ORDER BY	SUM(mf.size)
OPEN crs
FETCH NEXT FROM crs INTO @db_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	EXEC dbo.spBackupLog @database_name = @db_name, @verify = @verify, @label = @label

	FETCH NEXT FROM crs INTO @db_name
  END
CLOSE crs
DEALLOCATE crs

--- Look for any errors since the start time
SELECT	@iCount = COUNT(*) FROM dbo.tblMaintenance WHERE mnt_action_type = 'backup_log' AND mnt_success = 0 AND mnt_action_date >= @startTime
IF (@iCount > 0)
  BEGIN
	--- When there were errors, raise an error so an event is fired
	SELECT	@sError = COALESCE(@sError + ', ', '') + mnt_table_catalog FROM dbo.tblMaintenance WHERE mnt_action_type = 'backup_log' AND mnt_success = 0 AND mnt_action_date >= @startTime
	SELECT	@sError = 'The following databases had failures during transaction log backups: ' + ISNULL(@sError, 'N/A')

	RAISERROR(@sError, 16, 1)
  END

SET NOCOUNT OFF
